﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.IO;
using System.Linq;

namespace Citi.ExpenseReport.WebUI.Util
{
    internal class ExcelHelper
    {
        private const string XlsConnectionString =
            "{0}Provider=Microsoft.Jet.OLEDB.4.0;Data Source={1};Extended Properties={2};";
            //"{0}Provider=Microsoft.Jet.OLEDB.4.0;Data Source={1};Extended Properties=\"{2};HDR={3};IMEX={4};\";";

        private const string XlsxConnectionString =
            "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"{1};HDR={2};IMEX={3}\";";

        private const string XlsExcelVersion = "Excel 8.0";
        private const string XlsxExcelVersion = "Excel 12.0 Xml";

        public static OleDbConnection CreateConnection(string path)
        {
            return CreateConnection(path, true, true);
        }

        public static OleDbConnection CreateConnection(string path, bool intermixedRead)
        {
            return CreateConnection(path, intermixedRead, true);
        }

        public static OleDbConnection CreateConnectionOledb12(string path, bool intermixedRead, bool firstRowIsHeader)
        {
            if (!File.Exists(path))
                throw new FileNotFoundException("O arquivo informado não foi encontrado");

            var connectionString = string.Format(XlsxConnectionString, path, XlsxExcelVersion,
                                                 (firstRowIsHeader ? "Yes" : "No"), (intermixedRead ? "1" : "0"));
            var connection = new OleDbConnection(connectionString);
            connection.Open();

            return connection;
        }

        public static OleDbConnection CreateConnection(string path, bool intermixedRead, bool firstRowIsHeader)
        {
            OleDbConnection connection;

            if (!File.Exists(path))
                throw new FileNotFoundException("O arquivo informado não foi encontrado");

            var worksheetInfo = new FileInfo(path);
            string connectionString;
            if (string.Equals(worksheetInfo.Extension, ".xlsx", StringComparison.InvariantCultureIgnoreCase))
            {
                connectionString = string.Format(XlsxConnectionString, path, XlsxExcelVersion,
                                                 (firstRowIsHeader ? "Yes" : "No"), (intermixedRead ? "1" : "0"));
                connection = new OleDbConnection(connectionString);
                connection.Open();
            }
            else
            {
                try
                {
                    connectionString = string.Format(XlsConnectionString, "", path, XlsExcelVersion,
                                                     (firstRowIsHeader ? "Yes" : "No"), (intermixedRead ? "1" : "0"));
                    connection = new OleDbConnection(connectionString);
                    connection.Open();
                }
                catch
                {
                    connectionString = string.Format(XlsConnectionString, "OLEDB;", path, XlsExcelVersion,
                                                     (firstRowIsHeader ? "Yes" : "No"), (intermixedRead ? "1" : "0"));
                    connection = new OleDbConnection(connectionString);
                    connection.Open();
                }
            }

            return connection;
        }

        public static string[] GetSheetNames(OleDbConnection connection)
        {
            var dtSheets = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (dtSheets == null)
                return null;

            var excelSheets = new String[dtSheets.Rows.Count];
            var i = 0;
            foreach (DataRow row in dtSheets.Rows)
            {
                excelSheets[i] = row["TABLE_NAME"].ToString().Replace("$", "").Replace("'", "");

                if (excelSheets[i].Contains("#"))
                    continue;
                i++;
            }

            return excelSheets;
        }

        public static DataColumn[] GetSheetDataColumns(OleDbConnection connection, string sheetName)
        {
            var dtSheets = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null);

            if (dtSheets == null)
                return null;

            var columns = new List<DataColumn>();

            foreach (DataRow row in dtSheets.Rows)
            {
                var tableName = row["TABLE_NAME"].ToString().Replace("$", "");
                if (!string.Equals(tableName, sheetName, StringComparison.InvariantCultureIgnoreCase)) continue;
                var columnName = row["COLUMN_NAME"].ToString();
                var dataType = (OleDbType)Convert.ToInt32(row["DATA_TYPE"].ToString());

                Type columnType;
                switch (dataType)
                {
                    case OleDbType.Double:
                        columnType = typeof(double);
                        break;
                    case OleDbType.Currency:
                        columnType = typeof(decimal);
                        break;
                    case OleDbType.Date:
                        columnType = typeof(DateTime);
                        break;
                    case OleDbType.Boolean:
                        columnType = typeof(bool);
                        break;
                    case OleDbType.WChar:
                        columnType = typeof(string);
                        break;
                    default:
                        columnType = typeof(string);
                        break;
                }

                columns.Add(new DataColumn(columnName, columnType));
            }

            if (columns.Count == 0)
            {
                dtSheets = new DataTable();
                var sheetCommand = new OleDbCommand(String.Format("SELECT * FROM [{0}$]", sheetName.Replace("$", "")),
                                                    connection);
                var sheetAdapter = new OleDbDataAdapter(sheetCommand);
                sheetAdapter.Fill(dtSheets);

                columns.AddRange(dtSheets.Columns.Cast<DataColumn>());
            }

            return columns.ToArray();
        }

        public static Type GetTypeFromDbType(OleDbType dataType)
        {
            Type type;
            switch (dataType)
            {
                case OleDbType.Double:
                    type = typeof(double);
                    break;
                case OleDbType.Currency:
                    type = typeof(decimal);
                    break;
                case OleDbType.Date:
                    type = typeof(DateTime);
                    break;
                case OleDbType.Boolean:
                    type = typeof(bool);
                    break;
                case OleDbType.WChar:
                    type = typeof(string);
                    break;
                default:
                    type = typeof(string);
                    break;
            }

            return type;
        }

        public static OleDbType GetDbTypeFromType(Type type)
        {
            OleDbType dataType;

            if (type == typeof(double))
                dataType = OleDbType.Double;
            else if (type == typeof(decimal))
                dataType = OleDbType.Currency;
            else if (type == typeof(DateTime))
                dataType = OleDbType.Date;
            else if (type == typeof(bool))
                dataType = OleDbType.Boolean;
            else if (type == typeof(string))
                dataType = OleDbType.WChar;
            else
                dataType = OleDbType.WChar;

            return dataType;
        }

        public static string ValueToString(object value, Type type, CultureInfo language)
        {
            var returnValue = "null";

            if (value != null && value != DBNull.Value)
            {
                var dataType = GetDbTypeFromType(type);

                switch (dataType)
                {
                    case OleDbType.Double:
                        returnValue = ((double)value).ToString(language.NumberFormat).Replace(",", ".");
                        break;
                    case OleDbType.Currency:
                        returnValue = ((decimal)value).ToString(language.NumberFormat).Replace(",", ".");
                        break;
                    case OleDbType.Boolean:
                        returnValue = ((bool)value) ? "1" : "0";
                        break;
                    case OleDbType.Date:
                        //returnValue = "'" + ((DateTime)value).ToString(language.DateTimeFormat) + "'";
                        returnValue = "\"" + ((DateTime)value).ToString(language.DateTimeFormat) + "\"";
                        break;
                    case OleDbType.WChar:
                        //returnValue = "'" + value + "'";
                        returnValue = "\"" + value + "\"";
                        break;
                    default:
                        //returnValue = "'" + value + "'";
                        break;
                }
            }

            return returnValue;
        }
    }
}